import openpyxl
from openpyxl import load_workbook
import random
from datetime import datetime
import os


def get_materials_config(target_amount):
    """根据目标金额返回对应的材料配置"""
    if target_amount == 4500:
        return [
            {
                "row": 16,
                "name": "给水管(P)",
                "spec": "63",
                "unit_price": 68,
            },
            {"row": 17, "name": "直接", "spec": "63", "unit_price": 7},
            {"row": 18, "name": "弯头", "spec": "63", "unit_price": 12},
            {
                "row": 19,
                "name": "给水管(P)",
                "spec": "25",
                "unit_price": 11,
            },
            {"row": 20, "name": "直接", "spec": "25", "unit_price": 1},
            {"row": 21, "name": "弯头", "spec": "25", "unit_price": 1},
        ]
    else:
        # 3700、3500等使用原来的配置
        return [
            {
                "row": 15,
                "name": "给水管(P)",
                "spec": "63",
                "unit_price": 68,
            },
            {
                "row": 16,
                "name": "给水管(P)",
                "spec": "25",
                "unit_price": 11,
            },
            {"row": 17, "name": "直接", "spec": "63", "unit_price": 7},
            {"row": 18, "name": "直接", "spec": "25", "unit_price": 1},
            {"row": 19, "name": "弯头", "spec": "63", "unit_price": 12},
            {"row": 20, "name": "弯头", "spec": "25", "unit_price": 1},
        ]


def get_target_range(target_amount):
    """根据目标金额返回金额范围"""
    if target_amount == 3500:
        return (3500, 3550)
    elif target_amount == 4500:
        return (4500, 4550)
    else:
        # 3700、892等使用精确金额
        return (target_amount, target_amount)


def calculate_total_cost(material_subtotal):
    """计算总成本（材料费 + 工时费）"""
    labor_cost = round(material_subtotal * 0.3 * 1.09)
    return round(material_subtotal + labor_cost)


def process_single_budget(
    file_path, user_name, user_address, output_dir, target_amount=3700
):
    """处理单个用户的预算表"""
    # 加载工作簿，保留公式
    wb = load_workbook(file_path, data_only=False)
    ws = wb.active

    # 1. 修改首行，添加用户姓名和地址，并在公司名称后换行
    new_title = f"四川德运水务建设投资有限公司新政分公司\n{user_address}{user_name}给水材料预算表"
    ws["A1"] = new_title

    # 2. 修改日期为当天
    today = datetime.now().strftime("%Y.%m.%d")
    ws["F2"] = f"日期：{today}"

    # 3. 根据目标金额获取材料配置
    materials_to_adjust = get_materials_config(target_amount)
    target_min, target_max = get_target_range(target_amount)

    # 4. 计算非调整材料的费用
    non_adjustable_cost = 0
    adjustable_rows = [m["row"] for m in materials_to_adjust]

    # 确定检查的行范围（根据材料配置的最大行号）
    max_row = max(adjustable_rows) if adjustable_rows else 20
    for i in range(5, max_row + 2):  # +2 确保覆盖所有可能的行
        if i not in adjustable_rows:
            quantity = ws[f"E{i}"].value or 0
            unit_price = ws[f"F{i}"].value or 0
            try:
                non_adjustable_cost += float(quantity) * float(unit_price)
            except (ValueError, TypeError):
                print(f"警告: 无法计算第{i}行的费用")

    print(f"非调整材料费用: {non_adjustable_cost}")

    # 5. 使用算法找到达到目标金额的数量组合
    found_solution = False
    best_quantities = {}
    best_total = 0

    # 根据目标金额调整尝试次数和数量范围
    max_attempts = 10000
    if target_min != target_max:  # 对于范围目标，减少尝试次数
        max_attempts = 5000

    print(f"目标金额范围: {target_min} - {target_max}")

    for attempt in range(max_attempts):
        # 生成随机数量
        quantities = {}

        for material in materials_to_adjust:
            if "给水管" in material["name"]:
                # 给水管数量范围大一些
                quantities[material["row"]] = random.randint(5, 40)
            else:
                # 配件数量范围小一些
                quantities[material["row"]] = random.randint(1, 15)

        # 计算可调整材料的费用
        adjustable_cost = 0
        for material in materials_to_adjust:
            adjustable_cost += quantities[material["row"]] * material["unit_price"]

        # 计算材料费小计和总计
        material_subtotal = non_adjustable_cost + adjustable_cost
        total = calculate_total_cost(material_subtotal)

        # 检查是否达到目标金额
        if target_min <= total <= target_max:
            found_solution = True
            best_quantities = quantities
            best_total = total
            print(f"找到合适方案，尝试次数: {attempt+1}")
            break

    # 6. 如果没找到合适方案，使用默认方案并微调
    if not found_solution:
        print(f"未找到合适方案，将使用默认方案并微调")

        # 设置默认数量
        if target_amount == 4500:
            best_quantities = {16: 20, 17: 8, 18: 6, 19: 15, 20: 8, 21: 6}
        else:
            best_quantities = {15: 15, 16: 25, 17: 5, 18: 6, 19: 5, 20: 7}

        # 微调直到找到合适方案
        found_solution = fine_tune_quantities(
            best_quantities,
            materials_to_adjust,
            non_adjustable_cost,
            target_min,
            target_max,
        )

        if not found_solution:
            print("警告: 微调后仍未找到精确方案，使用默认方案")

    # 7. 应用找到的数量方案
    for material in materials_to_adjust:
        ws[f'E{material["row"]}'] = best_quantities[material["row"]]

    # 8. 计算最终结果
    final_adjustable_cost = 0
    print("\n最终材料数量:")
    for material in materials_to_adjust:
        qty = best_quantities[material["row"]]
        cost = qty * material["unit_price"]
        final_adjustable_cost += cost
        print(f"{material['name']} {material['spec']}: 数量={qty}, 金额={cost}")

    final_material_subtotal = non_adjustable_cost + final_adjustable_cost
    final_labor_cost = round(final_material_subtotal * 0.3 * 1.09)
    final_total = round(final_material_subtotal + final_labor_cost)

    print(f"\n材料费小计: {final_material_subtotal}")
    print(f"工时费: {final_labor_cost}")
    print(f"最终总计: {final_total}")

    # 9. 保存文件，文件名格式为"姓名_金额.xlsx"
    os.makedirs(output_dir, exist_ok=True)
    final_output_path = os.path.join(
        output_dir, f"预算表_{user_name}_{final_total}.xlsx"
    )
    wb.save(final_output_path)
    print(f"文件已保存到: {final_output_path}")

    return final_output_path, final_total


def fine_tune_quantities(
    quantities, materials, non_adjustable_cost, target_min, target_max
):
    """微调数量以找到合适方案"""
    # 按单价排序，优先调整高单价材料
    sorted_materials = sorted(materials, key=lambda x: x["unit_price"], reverse=True)

    for material in sorted_materials:
        original_qty = quantities[material["row"]]

        # 尝试增加和减少数量
        for adjustment in [-3, -2, -1, 1, 2, 3]:
            if adjustment == 0:
                continue

            temp_quantities = quantities.copy()
            new_qty = original_qty + adjustment

            if new_qty < 1:  # 数量不能小于1
                continue

            temp_quantities[material["row"]] = new_qty

            # 计算总金额
            adjustable_cost = 0
            for m in materials:
                adjustable_cost += temp_quantities[m["row"]] * m["unit_price"]

            material_subtotal = non_adjustable_cost + adjustable_cost
            total = calculate_total_cost(material_subtotal)

            if target_min <= total <= target_max:
                quantities.update(temp_quantities)
                print(f"通过微调 {material['name']} {material['spec']} 找到合适方案")
                return True

    return False


def process_multiple_budgets(file_path, user_list, output_dir, target_amount=3700):
    """批量处理多个用户的预算表"""
    # 验证目标金额
    valid_amounts = [3700, 3500, 4500]
    if target_amount not in valid_amounts:
        raise ValueError(f"目标金额必须是以下值之一: {valid_amounts}")

    results = []

    print(f"开始批量处理 {len(user_list)} 个用户的预算表...")
    print(f"目标金额: {target_amount}")
    print("=" * 50)

    for i, user_info in enumerate(user_list, 1):
        print(f"\n处理第 {i} 个用户: {user_info['name']}")
        print("-" * 30)

        output_path, total = process_single_budget(
            file_path,
            user_info["name"],
            user_info["address"],
            output_dir,
            target_amount,
        )

        results.append(
            {
                "name": user_info["name"],
                "address": user_info["address"],
                "file_path": output_path,
                "total": total,
            }
        )

    print("\n" + "=" * 50)
    print("批量处理完成！")
    print(f"成功生成 {len(results)} 个预算表文件")

    # 打印汇总信息
    print("\n生成的文件列表:")
    for result in results:
        print(f"- {result['name']}: {result['file_path']}")

    return results


# 使用示例
if __name__ == "__main__":
    # 输入参数
    input_file = "./input/3700预算模板.xlsx"
    output_dir = "./output"

    # 定义多个用户信息
    users = [
        {"name": "何纯荣", "address": "新政镇六一二安置区"},
        # {"name": "张三", "address": "某地址"},
    ]

    # 批量处理 - 可以更改目标金额
    target_amount = 3700  # 可以改为 3500, 4500
    results = process_multiple_budgets(input_file, users, output_dir, target_amount)
